-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--

-- --------------------------------------------------------

--
-- 表的结构 'cash_dividend_flow'
--

/*CREATE  TABLE cash_dividend_flow (
  event_date date NOT NULL,
  ticker varchar(20) NOT NULL,
  security_type varchar(8), 
  portfolio_id varchar(8) NOT NULL,
  amount number(16,2) NOT NULL,
  currency char(3) DEFAULT 'CNY' NOT NULL 
);

-- --------------------------------------------------------

- --------------------------------------------------------

--
-- 表的结构 'comm_fx'
--
/*
CREATE  TABLE comm_fx (
  ref_date date NOT NULL,
  dom_curncy char(3) NOT NULL,
  for_curncy char(3) NOT NULL,
  price number(14,10) NOT NULL,
  PRIMARY KEY (ref_date,dom_curncy,for_curncy)
);
*/
-- --------------------------------------------------------

-- --------------------------------------------------------

CREATE TABLE official_fx_bk 	
(ref_date date NOT NULL,
  for_curncy char(3) NOT NULL,
  price number(16,10) NOT NULL)

--
-- 表的结构 'comm_misc_static_info'
--
/*
CREATE  TABLE comm_misc_static_info (
  ticker varchar(20) NOT NULL,
  field varchar(20) NOT NULL,
  "value" number(8,2) DEFAULT NULL,
  str_value varchar(20) DEFAULT NULL,
  update_time timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (ticker,field)
);*/

-- --------------------------------------------------------

--
-- 表的结构 'comm_security_daily_price'
--

/*
CREATE  TABLE comm_security_daily_price (
  ref_date date NOT NULL,
  ticker varchar(20) NOT NULL,
  close_price number(12,4) NOT NULL,
  PRIMARY KEY (ref_date,ticker)
);
*/

-- --------------------------------------------------------


--
-- 表的结构 'comm_security_static_info'
--

/*CREATE  TABLE comm_security_static_info (
  ticker varchar(20) NOT NULL,
  "name" varchar(40),
  market varchar(20),
  PRIMARY KEY (ticker)
);*/

-- --------------------------------------------------------



--
-- 表的结构 'pnote_info'
--
/*
CREATE  TABLE pnote_info (
  isin_code varchar(20) NOT NULL,
  underlying_ticker varchar(20) NOT NULL,
  currency char(3) NOT NULL,
  underlying_currency char(3) NOT NULL,
  underlying_type varchar(8) NOT NULL
  PRIMARY KEY (isin_code)
);
*/
-- --------------------------------------------------------

--
-- 表的结构 'portfolio_info'
--

/*CREATE  TABLE portfolio_info (
  portfolio_id varchar(8) NOT NULL,
  "name" varchar(16) NOT NULL,
  inception_date date NOT NULL,
  inception_val number(16,2) NOT NULL,
  currency char(3) NOT NULL,
  manager varchar(16) NOT NULL,
  "type" varchar(16) NOT NULL,
  cash_account number(12,0) DEFAULT NULL,
  expiry_date date DEFAULT NULL,
  parent_portf varchar(8) DEFAULT NULL,
  PRIMARY KEY (portfolio_id)
);

-- --------------------------------------------------------

--
-- 表的结构 'portfolio_mkt_val'
--

CREATE  TABLE portfolio_mkt_val (
  ref_date date NOT NULL,
  portfolio_id varchar(8) NOT NULL,
  mkt_val number(16,2) NOT NULL,
  mkt_val_post number(16,2) NOT NULL,
  currency char(3) NOT NULL,
  PRIMARY KEY (ref_date,portfolio_id)
);

-- --------------------------------------------------------

--
-- 表的结构 'position'
--

CREATE  TABLE position (
  ref_date date NOT NULL,
  portfolio_id varchar(8) NOT NULL,
  ticker varchar(20) DEFAULT '' NOT NULL,
  security_type varchar(8) DEFAULT 'STOCK' NOT NULL,
  amount number(12,2) NOT NULL,
  avg_cost_price number(20,10) NOT NULL,
  price_currency char(3) DEFAULT 'CNY' NOT NULL,
  market varchar(20),
  PRIMARY KEY (ref_date,portfolio_id,ticker,security_type,price_currency)
);

-- --------------------------------------------------------

CREATE GLOBAL TEMPORARY TABLE tmp_daily_position (
  ref_date date NOT NULL,
  portfolio_id varchar(8) NOT NULL,
  ticker varchar(20) DEFAULT '' NOT NULL,
  security_type varchar(8) DEFAULT 'STOCK' NOT NULL,
  amount number(12,2) NOT NULL,
  avg_cost_price number(20,10) NOT NULL,
  price_currency char(3) DEFAULT 'CNY' NOT NULL,
  market varchar(20)
)
ON COMMIT PRESERVE ROWS;

--
-- 表的结构 'repo_info'
--

CREATE  TABLE repo_info (
  ticker varchar(20) NOT NULL,
  start_date date NOT NULL,
  maturity_date date NOT NULL,
  currency char(3) NOT NULL,
  daycount number(2) default 7,
  basis number(3) default 360,
  PRIMARY KEY (start_date,ticker)
);*/

-- --------------------------------------------------------

--
-- 表的结构 'security_type_rank'
--

/*CREATE  TABLE security_type_rank (
  sec_type varchar(20) NOT NULL,
  rank number(2,0) NOT NULL,
  PRIMARY KEY (sec_type)
);
*/
-- --------------------------------------------------------

--
-- 表的结构 'stock_dividend_flow'
--
/*
CREATE  TABLE stock_dividend_flow (
  event_date date NOT NULL,
  ticker varchar(20) NOT NULL,
  portfolio_id varchar(8) NOT NULL,
  amount number(20,0) NOT NULL
);*/

-- --------------------------------------------------------

--
-- 表的结构 'transaction'
--
/*CREATE SEQUENCE SEQ_TRD
MINVALUE 1
NOMAXVALUE
START WITH 1
INCREMENT BY 1
NOCACHE;


CREATE  TABLE "transaction" (
  trade_id number(12) NOT NULL,
  trade_date date NOT NULL,
  ticker varchar(20) NOT NULL,
  trade_type varchar(20) NOT NULL,
  portfolio_id varchar(8) NOT NULL,
  security_type varchar(8) DEFAULT 'STOCK',
  amount number(12) NOT NULL,
  cost_price number(20,10) NOT NULL,
  currency char(3) NOT NULL,
  market varchar(20) DEFAULT NULL,
  settle_date date, 
  PRIMARY KEY (trade_id) --,
--  KEY trade_property (trade_date,ticker,portfolio_id)
);

create index trade_property on transaction(trade_date,ticker,portfolio_id);

---create Oracle trigger
create or replace trigger TRG_TRD before
insert on transaction for each row
begin
select SEQ_TRD.nextval into :new.trade_id from dual;
end;

alter table transaction add constraint UK_TRD unique (trade_date,ticker,portfolio_id,security_type);

*/
-- --------------------------------------------------------


create table cash_adj
(
  ref_date date NOT NULL,
  portfolio_id varchar(8) NOT NULL,
  amount number(12,2) NOT NULL,
  currency char(3) NOT NULL,
  adj_type varchar(20) NOT NULL,
  detail varchar(128)
)

CREATE GLOBAL TEMPORARY TABLE tmp_dividend 	
(ticker VARCHAR(20) NOT NULL, 
cash_divd number(10,8), 
currency CHAR(3), 
share_divd number(10,8),
cash_divd_extax number(8,6))
ON COMMIT PRESERVE ROWS


CREATE TABLE dividend_supplement
(ref_date date NOT NULL, 
ticker VARCHAR(20) NOT NULL, 
cash_divd number(10,8), 
currency CHAR(3), 
share_divd number(10,8),
cash_divd_extax number(8,6))


CREATE VIEW comm_calendar
AS
select to_date('20070101', 'yyyymmdd') + level - 1 as ref_date
from dual
connect by level <= (current_date - to_date('20070101', 'yyyymmdd') + 1)
WITH READ ONLY;


create view repo_info_ex
as 
select ticker,start_date,maturity_date,currency,
NVL(daycount,maturity_date-start_date)/100/basis tfraction
from repo_info
with read only;

create table repo_fee
(
ticker VARCHAR(20) NOT NULL, 
commission_fee number(8,6),
PRIMARY KEY (ticker)
);


CREATE VIEW comm_fx_latest
AS  
select tmp.ref_date,
	fx.dom_curncy,
	fx.for_curncy,
	fx.price 
from comm_fx fx
join (
	select cld.ref_date,
	  max(fx2.ref_date) av_date,
	  fx2.dom_curncy,
	  fx2.for_curncy
	from comm_calendar cld
	join comm_fx fx2 
		on fx2.ref_date<=cld.ref_date 
	group by cld.ref_date,fx2.dom_curncy,fx2.for_curncy
) tmp 
on fx.ref_date=tmp.av_date 
  and fx.dom_curncy=tmp.dom_curncy 
  and fx.for_curncy=tmp.for_curncy
WITH READ ONLY;


create view comm_fx_latest2
as
select * from (
select cld.ref_date ref_date, 
	fx.dom_curncy,
	fx.for_curncy,
    fx.price, 
  row_number() over(partition by cld.ref_date,fx.dom_curncy,fx.for_curncy order by fx.ref_date desc) rk 
from comm_fx fx
join comm_calendar cld
  on cld.ref_date>=fx.ref_date
)
where rk=1
with read only;


-- --------------------------------------------------------

CREATE VIEW comm_security_latest_price
AS  
select tmp.ref_date,p.ticker,p.close_price
from comm_security_daily_price p
join (
	select cld.ref_date, max(dp.ref_date) av_date, dp.ticker 
	from comm_calendar cld
	join comm_security_daily_price dp 
		on dp.ref_date<=cld.ref_date 
	group by cld.ref_date,dp.ticker
) tmp 
on p.ticker=tmp.ticker 
  and p.ref_date=tmp.av_date
WITH READ ONLY;



CREATE VIEW daily_position
AS  
select tmp.ref_date,
       p.portfolio_id,
       p.ticker,
       p.security_type,
       p.amount,
       p.avg_cost_price,  
       p.price_currency,
       p.market
from position p
join (
	select cld.ref_date,
		max(p2.ref_date) av_date,
		p2.portfolio_id,
		p2.security_type,
		p2.ticker,
		p2.price_currency
	from comm_calendar cld
	join position p2 
	  on p2.ref_date<=cld.ref_date 
	group by cld.ref_date,
			p2.portfolio_id,
			p2.security_type,
			p2.ticker,
			p2.price_currency
) tmp 
on p.ref_date=tmp.av_date 
	and p.portfolio_id=tmp.portfolio_id
	and p.security_type=tmp.security_type
	and p.ticker=tmp.ticker
	and p.price_currency=tmp.price_currency
where p.security_type='CASH' or p.amount <> 0
WITH READ ONLY;
-- --------------------------------------------------------


--
-- 视图结构 'daily_position_ex'
--

CREATE VIEW daily_position_tick_ex 
AS 
select dp.ref_date AS ref_date,dp.portfolio_id AS portfolio_id,dp.ticker AS ticker,dp.security_type AS security_type,dp.amount AS amount,dp.avg_cost_price AS avg_cost_price,dp.price_currency AS price_currency,dp.market AS market,
       decode(dp.security_type,'PNOTE',pi.underlying_ticker,dp.ticker) AS lticker 
from 
daily_position dp 
left join pnote_info pi 
on pi.isin_code = dp.ticker
WITH READ ONLY;
-- --------------------------------------------------------



--
-- 视图结构 'mkt_expo'
--

CREATE VIEW mkt_expo AS select mark_to_mkt.ref_date AS ref_date,mark_to_mkt.portfolio_id AS portfolio_id,mark_to_mkt.ticker AS ticker,mark_to_mkt.security_type AS security_type,mark_to_mkt.mkt_val AS exposure,mark_to_mkt.local_curncy AS local_curncy from mark_to_mkt where ((mark_to_mkt.security_type = 'STOCK') or (mark_to_mkt.security_type = 'PNOTE')) union select dp.ref_date AS ref_date,dp.portfolio_id AS portfolio_id,dp.ticker AS ticker,dp.security_type AS security_type,((dp.amount * p.close_price) * si."value") AS exposure,dp.price_currency AS local_curncy from ((daily_position dp join comm_misc_static_info si on(((dp.security_type = 'FUTURES') and (si.ticker = dp.ticker) and (si.field = 'FUT_VAL_PT')))) join comm_security_latest_price p on(((dp.ref_date = p.ref_date) and (dp.ticker = p.ticker))))
WITH READ ONLY;




-- --------------------------------------------------------

--
-- 视图结构 'transaction_ex'
--

CREATE VIEW transaction_tick_ex AS select trd.trade_id AS trade_id,trd.trade_date AS trade_date,trd.ticker AS ticker,trd.trade_type AS trade_type,trd.portfolio_id AS portfolio_id,trd.security_type AS security_type,trd.amount AS amount,trd.cost_price AS cost_price,trd.currency AS currency,trd.market AS market,decode(trd.security_type,'PNOTE',pi.underlying_ticker,trd.ticker) AS lticker from (transaction trd left join pnote_info pi on((pi.isin_code = trd.ticker)))
WITH READ ONLY;



insert into comm_dividend_preserved
values(TO_DATE('2012-09-05','yyyy-mm-dd'),'SNP UN Equity',1.575879,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2012-05-16','yyyy-mm-dd'),'SNP UN Equity',3.189967,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2011-09-07','yyyy-mm-dd'),'SNP UN Equity',1.562259,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2011-06-08','yyyy-mm-dd'),'SNP UN Equity',1.996633,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2010-09-01','yyyy-mm-dd'),'SNP UN Equity',1.18026,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2010-06-02','yyyy-mm-dd'),'SNP UN Equity',1.610148,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2009-09-10','yyyy-mm-dd'),'SNP UN Equity',1.024476,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2009-06-03','yyyy-mm-dd'),'SNP UN Equity',0.157413,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2009-06-03','yyyy-mm-dd'),'SNP UN Equity',1.161245,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2008-09-10','yyyy-mm-dd'),'SNP UN Equity',0.438766,'USD',0.0);
insert into comm_dividend_preserved
values(TO_DATE('2008-06-04','yyyy-mm-dd'),'SNP UN Equity',1.652434,'USD',0.0);
